﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class CompetitionsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoCompetitions model)
        {
            model.COM_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Competitions]");
            sql.Append("([COM_ID],[COM_TYPE],[COM_NAME],[COM_ACADEMY],[COM_MANAGER],[COM_SEMESTER],[COM_FORM],[COM_SPECIALTY],[COM_HOST],[COM_UNDERTAKE],[COM_LEVEL],[COM_CAPITAL],[COM_CONTENTS],[COM_ISSHOW])");
            sql.Append("VALUES");
            sql.Append("(@COM_ID,@COM_TYPE,@COM_NAME,@COM_ACADEMY,@COM_MANAGER,@COM_SEMESTER,@COM_FORM,@COM_SPECIALTY,@COM_HOST,@COM_UNDERTAKE,@COM_LEVEL,@COM_CAPITAL,@COM_CONTENTS,@COM_ISSHOW)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoCompetitions model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Competitions] SET ");
            sql.Append("COM_TYPE=@COM_TYPE");
            sql.Append(",COM_NAME=@COM_NAME");
            sql.Append(",COM_ACADEMY=@COM_ACADEMY");
            sql.Append(",COM_MANAGER=@COM_MANAGER");
            sql.Append(",COM_SEMESTER=@COM_SEMESTER");
            sql.Append(",COM_FORM=@COM_FORM");
            sql.Append(",COM_SPECIALTY=@COM_SPECIALTY");
            sql.Append(",COM_HOST=@COM_HOST");
            sql.Append(",COM_UNDERTAKE=@COM_UNDERTAKE");
            sql.Append(",COM_LEVEL=@COM_LEVEL");
            sql.Append(",COM_CAPITAL=@COM_CAPITAL");
            sql.Append(",COM_CONTENTS=@COM_CONTENTS");
            sql.Append(",COM_ISSHOW=@COM_ISSHOW");
            sql.Append(" WHERE ");
            sql.Append("COM_ID=@COM_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }
        /// <summary>
        /// 通过学院id来删除竞赛项目
        /// </summary>
        /// <param name="academyId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteCompetitionsByAcademyId(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Competitions] SET");
            sql.Append(" COM_IsShow='false' ");
            sql.Append("WHERE");
            sql.Append(" COM_ACADEMY=@COM_ACADEMY");
            SqlParameter[] parameter=new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ACADEMY",SqlDbType.NVarChar,50);
            parameter[0].Value = academyId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);

        }
       
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string cOM_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Competitions] SET");
            sql.Append(" COM_ISSHOW='false'");
            sql.Append(" WHERE ");
            sql.Append("COM_ID=@COM_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cOM_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoCompetitions GetModel(string cOM_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_LEVEL,COM_CAPITAL,COM_CONTENTS,ACA_NAME,TEA_NAME,COM_ISSHOW ");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID ");
            sql.Append(" WHERE COM_ID=@COM_ID AND COM_ISSHOW='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cOM_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoCompetitions> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoCompetitions> GetAllCompetitions()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_CAPITAL,COM_LEVEL,COM_CONTENTS,ACA_NAME,TEA_NAME ,COM_ISSHOW");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID ");
            sql.Append("WHERE COM_ISSHOW='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoCompetitions> list = GetList(table);
            return list;
        }

        public IList<BoCompetitions> GetCompetitionsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_CAPITAL,COM_LEVEL,COM_CONTENTS,ACA_NAME,TEA_NAME,COM_ISSHOW ");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID ");
            sql.Append(" WHERE COM_ACADEMY=@COM_ACADEMY AND COM_ISSHOW='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ACADEMYD", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoCompetitions> list = GetList(table);
            return list;
        }

        public IList<BoCompetitions> GetCompetitionsByType(int comType)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_LEVEL,COM_CAPITAL,COM_CONTENTS,ACA_NAME,TEA_NAME,COM_ISSHOW ");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID ");
            sql.Append(" WHERE COM_TYPE=@COM_TYPE AND COM_ISSHOW='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_TYPE", SqlDbType.Int);
            parameter[0].Value = comType;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoCompetitions> list = GetList(table);
            return list;
        }

        public IList<BoCompetitions> GetCompetitionsByTypeAndAcademy(int comType, string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_LEVEL,COM_CAPITAL,COM_CONTENTS,ACA_NAME,TEA_NAME,COM_ISSHOW ");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID ");
            sql.Append(" WHERE COM_TYPE=@COM_TYPE AND COM_ACADEMY=@COM_ACADEMY AND COM_ISSHOW='true'");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@COM_TYPE", SqlDbType.Int);
            parameter[0].Value = comType;
            parameter[1] = new SqlParameter("@COM_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[1].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoCompetitions> list = GetList(table);
            return list;
        }

        private List<BoCompetitions> GetList(DataTable table)
        {
            List<BoCompetitions> list = new List<BoCompetitions>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoCompetitions model = new BoCompetitions();
                    model.COM_ID = table.Rows[i]["COM_ID"]==System .DBNull .Value ?"":(string)table.Rows[i]["COM_ID"];
                    model.COM_TYPE = table.Rows[i]["COM_TYPE"]==System .DBNull .Value ?0:(Int32)table.Rows[i]["COM_TYPE"];
                    model.COM_NAME = table.Rows[i]["COM_NAME"]==System .DBNull .Value ?"":(string)table.Rows[i]["COM_NAME"];
                    model.COM_ACADEMY = table.Rows[i]["COM_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_ACADEMY"];
                    model.COM_MANAGER = table.Rows[i]["COM_MANAGER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_MANAGER"];
                    model.COM_SEMESTER = table.Rows[i]["COM_SEMESTER"] == System.DBNull.Value ? -1 : (Int32)table.Rows[i]["COM_SEMESTER"];
                    model.COM_FORM = table.Rows[i]["COM_FORM"]==System .DBNull .Value ?0:(Int32)table.Rows[i]["COM_FORM"];
                    model.COM_SPECIALTY = table.Rows[i]["COM_SPECIALTY"]==System .DBNull .Value ?0:(Int32)table.Rows[i]["COM_SPECIALTY"];
                    model.COM_HOST = table.Rows[i]["COM_HOST"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_HOST"];
                    model.COM_UNDERTAKE = table.Rows[i]["COM_UNDERTAKE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_UNDERTAKE"];
                    model.COM_LEVEL = table.Rows[i]["COM_LEVEL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_LEVEL"];
                    model.COM_CONTENTS = table.Rows[i]["COM_CONTENTS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_CONTENTS"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    model.TEA_NAME = table.Rows[i]["TEA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["TEA_NAME"];
                    model.COM_CAPITAL = table.Rows[i]["COM_CAPITAL"] == System.DBNull.Value ? 0 : (int)table.Rows[i]["COM_CAPITAL"];
                    model.COM_ISSHOW = table.Rows[i]["COM_ISSHOW"] == System.DBNull.Value ? true : (bool)table.Rows[i]["COM_ISSHOW"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoCompetitions model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@COM_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_TYPE",SqlDbType.Int,4),
                new SqlParameter("@COM_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_ACADEMY",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_MANAGER",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_SEMESTER",SqlDbType.Int,4),
                new SqlParameter("@COM_FORM",SqlDbType.Int,4),
                new SqlParameter("@COM_SPECIALTY",SqlDbType.Int,4),
                new SqlParameter("@COM_HOST",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_UNDERTAKE",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_LEVEL",SqlDbType.NVarChar,50),
                new SqlParameter("@COM_CAPITAL",SqlDbType.Int,4),
                new SqlParameter ("@COM_CONTENTS",SqlDbType.Text),
                new SqlParameter ("@COM_ISSHOW",SqlDbType .Bit,2 )
            };
            parameters[0].Value = model.COM_ID;
            parameters[1].Value = model.COM_TYPE;
            parameters[2].Value = model.COM_NAME;
            parameters[3].Value = model.COM_ACADEMY;
            parameters[4].Value = model.COM_MANAGER;
            parameters[5].Value = model.COM_SEMESTER;
            parameters[6].Value = model.COM_FORM;
            parameters[7].Value = model.COM_SPECIALTY;
            parameters[8].Value = model.COM_HOST;
            parameters[9].Value = model.COM_UNDERTAKE;
            parameters[10].Value = model.COM_LEVEL;
            parameters[11].Value = model.COM_CAPITAL;
            parameters[12].Value = model.COM_CONTENTS;
            parameters[13].Value = model.COM_ISSHOW;
            return parameters;
        }
         /// <summary>
        /// 根据条件选择前top个
        /// </summary>
        /// <param name="top">前几个</param>
        /// <param name="constr">条件</param>
        /// <returns>返回list对象表</returns>
        public DataTable getAllCompetitions(int top, string constr)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (top != 0)
                sql.Append("top " + top);
            sql.Append(" * from Competitions where 1=1 ");
            if (!string.IsNullOrEmpty(constr))
                sql.Append("and " + constr);
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            return table;
        }

         /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public DataTable getPageCompetitions(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COM_ID,COM_TYPE,COM_NAME,COM_ACADEMY,COM_MANAGER,COM_SEMESTER,COM_FORM,COM_SPECIALTY,COM_HOST,COM_UNDERTAKE,COM_CAPITAL,COM_LEVEL,COM_CONTENTS,ACA_NAME,TEA_NAME,COM_ISSHOW ");
            sql.Append(" FROM Competitions AS C LEFT JOIN Academy AS A ON C.COM_ACADEMY=A.ACA_ID LEFT JOIN Teachers AS T ON C.COM_MANAGER=T.TEA_ID  where COM_ISSHOW='true' "); 
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            return table;
        }

        /// <summary>
        /// 通过竞赛任务的id来获取竞赛项目的id
        /// </summary>
        /// <param name="Con_Id">竞赛任务的id</param>
        /// <returns>返回竞赛项目的对象</returns>
        public BoCompetitions getCompetitionsByContest(string Con_Id)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT * FROM v_Contest WHERE COM_ISSHOW='true' AND CON_ID = @CON_ID ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = Con_Id;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoCompetitions> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
    }
}